﻿Imports System
Imports IDServer
Imports System.Threading.Thread
Imports System.IO
Imports System.String
Imports Microsoft.VisualBasic
Imports Microsoft.VisualBasic.VBMath
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports CalendarColumn
Imports System.Configuration

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class Frm_Rate

    Implements IDServer.IDServer.IDServer

    Public ActionHelp As IDServer.IDServer.ActionBtn = IDServer.IDServer.ActionBtn.ActionHelp

    Public mDS As DataSet  '新增表單的的資料集
    Private dsBSC As DataSet  '存放公共資料
    Private ds As New DataSet  '瀏覽表單的資料集
    Private dsTemp As New DataSet '更新剩餘數量及剩餘金額時用
    Private strOriginalNum As String = ""
    Private strOrFNum As String = ""
    Private strOriginalFSaleOrdeNo As String = "" '原始銷售合同
    Private tempDS As New DataSet
    Private dsINV As New DataSet
    Private bFind As Boolean
    Private Fields As String
    Private BWB As String
    Private WithEvents MainFrom As Frm_MainForm

    Private mPoint As Point
    Private X, Y As Integer
    Private _Years As String = ""
    Private _Months As String = ""



#Region "自定義屬性"
    Public Property SelectObject() As Frm_MainForm
        Get
            Return MainFrom
        End Get
        Set(ByVal value As Frm_MainForm)
            MainFrom = value
        End Set
    End Property

    ''' <summary>
    ''' 獲取或設置當前選擇的年份
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Property PYears() As String
        Get
            Return _Years
        End Get
        Set(ByVal value As String)
            _Years = value
        End Set
    End Property

    ''' <summary>
    ''' 獲取或設置當前選擇的月份
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Property PMonths() As String
        Get
            Return _Months
        End Get
        Set(ByVal value As String)
            _Months = value
        End Set
    End Property
#End Region

#Region "inteface define"
    Public Function ActionAdd() As Boolean Implements IDServer.IDServer.IDServer.ActionAdd
        Try
            If AddEnable = False Then
                MessageBox.Show("對不起，您沒有新增權限", Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Function
            End If

            Me.Cursor = Cursors.WaitCursor

            ClearText()
            ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd

            EnabledControls(True)

            Me.TabControl1.SelectedIndex = 1

            SetButton(ButtonType.Add, False)
            setDgvEdit()
            Me.txtYears.Focus()

        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox("ActionAdd：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        Finally
            Me.Cursor = Cursors.Default
        End Try
    End Function

    Public Function ActionCancel() As Boolean Implements IDServer.IDServer.IDServer.ActionCancel
        Try
            Dim Num As String


            ActionHelp = IDServer.IDServer.ActionBtn.ActionHelp

            If ds Is Nothing Then
                Exit Function
            End If

            ClearText()
            EnabledControls(False)
            IsFind = FindRecord()
            SetButton(ButtonType.Cancel, IsFind)

            'If Me.dgv1.SelectedCells.Count > 0 Then
            '    Num = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("FNum").Value
            '    FillText(Num)
            'End If
            'Me.dgv1_CurrentCellChanged(Nothing, Nothing)
            'GetToolEnable()


        Catch ex As Exception
            MsgBox("ActionCancel：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    Public Function ActionDelete() As Boolean Implements IDServer.IDServer.IDServer.ActionDelete
        Dim cnSqlServer As New SqlConnection
        cnSqlServer = GetConn()
        Dim cmdSqlServer As New SqlCommand
        Dim trnSqlServer As SqlTransaction = cnSqlServer.BeginTransaction
        'Dim strNum As String
        'Dim iIndex As Integer

        Dim Years, Months, CurrencyID_f, CurrencyID_t As String
        Dim strCurrencyID_f, strCurrencyID_t As String

        Try
            If DeleteEnable = False Then
                MessageBox.Show("對不起，您沒有刪除權限", Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Function
            End If

            If ds Is Nothing Then
                Exit Function
            End If

            If ds.Tables(0).Rows.Count = 0 Then
                Exit Function
            End If

            Years = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value.ToString.Trim & ""
            Months = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value.ToString.Trim & ""

            strCurrencyID_f = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("CurrencyID_f").Value.ToString.Trim & ""
            strCurrencyID_t = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("CurrencyID_t").Value.ToString.Trim & ""

            CurrencyID_f = GetCurrencyID(Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("CurrencyID_f").Value)
            CurrencyID_t = GetCurrencyID(Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("CurrencyID_t").Value)

            If MsgBox("確實要刪除此匯率嗎？", MsgBoxStyle.Question Or MessageBoxButtons.YesNo, "資訊提示") = MsgBoxResult.Yes Then
                With cmdSqlServer
                    .Connection = cnSqlServer
                    .Transaction = trnSqlServer
                    .CommandType = CommandType.Text

                    '刪除記錄
                    .CommandText = "DELETE BSC_RateA WHERE Years='" & Years & "' AND Months='" & Months & _
                                 "' AND CurrencyID_f='" & CurrencyID_f & "' AND CurrencyID_t='" & CurrencyID_t & "'"
                    .ExecuteNonQuery()
                End With
                trnSqlServer.Commit()


                '清空DS中相關記錄
                For I As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1
                    Dim mRow As DataRow
                    mRow = ds.Tables(0).Rows(I)
                    If mRow.Item("Years").ToString.Trim = Years And mRow.Item("Months").ToString.Trim = Months _
                     And mRow.Item("CurrencyID_f").ToString.Trim = strCurrencyID_f And mRow.Item("CurrencyID_t").ToString.Trim = strCurrencyID_t Then
                        mRow.Delete()
                        ds.AcceptChanges()
                    End If
                Next

                dgv1.Refresh()

                IsFind = FindRecord()
                SetButton(ButtonType.Browse, IsFind)

                If IsFind = False Then
                    For I As Integer = 0 To tempDS.Tables.Count - 1
                        tempDS.Tables(I).Rows.Clear()
                    Next
                End If

                MsgBox("刪除成功！", MsgBoxStyle.OkOnly, "資訊提示")
            Else
                trnSqlServer.Rollback()

            End If


        Catch ex As Exception
            trnSqlServer.Rollback()
            MsgBox("ActionDelete：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)

        End Try
    End Function

    Public Function ActionEdit() As Boolean Implements IDServer.IDServer.IDServer.ActionEdit
        Try
            If EditEnable = False Then
                MessageBox.Show("對不起，您沒有修改權限", Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Function
            End If

            Dim Years As String
            Dim Months As String

            If ds Is Nothing Then
                Exit Function
            End If

            If ds.Tables(0).Rows.Count = 0 Then
                Exit Function
            End If

            If Me.dgv1.SelectedCells.Count = 0 Then
                Exit Function
            End If

            If Me.TabControl1.SelectedIndex = 0 Then
                Me.TabControl1.SelectedIndex = 1
            End If

            If Me.dgv1.SelectedCells.Count > 0 Then
                Years = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value
                Months = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value
                FillText(Years, Months)

            End If

            ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit
            EnabledControls(True)
            SetButton(ButtonType.Edit, False)

        Catch ex As Exception
            MsgBox("ActionEdit:" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    Public Function ActionExport() As Boolean Implements IDServer.IDServer.IDServer.ActionExport
        Try
            Me.Cursor = Cursors.WaitCursor
            If FindRecord() = False Then
                Exit Function
            Else

                Dim tb As DataTable '猁換腔桶

                Dim dsTemp As DataSet = ds.Copy
                '將列名改為中文名
                For I As Integer = Me.dgv1.Columns.Count - 1 To 0 Step -1
                    Dim strName As String = Me.dgv1.Columns(I).Name
                    dsTemp.Tables(0).Columns(strName).Caption = Me.dgv1.Columns(strName).HeaderText
                    '移除不需要顯示的列
                    If Me.dgv1.Columns(I).Visible = False Then
                        dsTemp.Tables(0).Columns.Remove(strName)
                    End If
                Next


                tb = ds.Tables(1).Copy

                '將列名改為中文名
                For I As Integer = Me.dgv2.Columns.Count - 1 To 0 Step -1
                    Dim strName As String = Me.dgv2.Columns(I).Name
                    dsTemp.Tables(1).Columns(strName).Caption = Me.dgv2.Columns(strName).HeaderText
                    '移除不需要顯示的列
                    If Me.dgv2.Columns(I).Visible = False Then
                        dsTemp.Tables(1).Columns.Remove(strName)
                    End If
                Next

                '將表名改為中文名
                dsTemp.Tables(0).TableName = "月匯率"
                dsTemp.Tables(1).TableName = "日匯率"


                SaveXls(dsTemp)

            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox("ActionExport：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        Finally
            Me.Cursor = Cursors.Default
        End Try
    End Function

    Public Function ActionFilter() As Boolean Implements IDServer.IDServer.IDServer.ActionFilter

    End Function

    Public Function ActionGoFirst() As Boolean Implements IDServer.IDServer.IDServer.ActionGoFirst
        Try
            If ds Is Nothing Then
                Exit Function
            End If

            If ds.Tables.Count = 0 Then
                Exit Function
            End If

            If ds.Tables(0).Rows.Count = 0 Then
                Exit Function
            End If

            Dim row As Integer = Me.dgv1.CurrentCell.RowIndex

            If Me.dgv1.RowCount > 0 And Me.dgv1.CurrentRow.Index > 0 Then
                Me.dgv1.CurrentCell = Me.dgv1.Rows(0).Cells(0)
                If Me.TabControl1.SelectedIndex = 1 Then
                    If ActionHelp = IDServer.IDServer.ActionBtn.ActionHelp Then
                        FillText(Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value, Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value)
                    End If
                End If

            End If
        Catch ex As Exception
            MsgBox("ActionGoFirst：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    Public Function ActionGoLast() As Boolean Implements IDServer.IDServer.IDServer.ActionGoLast
        Try
            If ds Is Nothing Then
                Exit Function
            End If
            Dim row As Integer = Me.dgv1.CurrentCell.RowIndex

            If ds.Tables.Count = 0 Then
                Exit Function
            End If

            If ds.Tables(0).Rows.Count = 0 Then
                Exit Function
            End If

            If Me.dgv1.RowCount > 0 Then
                Me.dgv1.CurrentCell = Me.dgv1.Rows(Me.dgv1.Rows.Count - 1).Cells(0)
                If Me.TabControl1.SelectedIndex = 1 Then
                    If ActionHelp = IDServer.IDServer.ActionBtn.ActionHelp Then
                        FillText(Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value, Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value)
                    End If
                End If
            End If
        Catch ex As Exception
            MsgBox("ActionGoLast：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    Public Function ActionGoNext() As Boolean Implements IDServer.IDServer.IDServer.ActionGoNext
        Try
            If ds Is Nothing Then
                Exit Function
            End If

            If ds.Tables.Count = 0 Then
                Exit Function
            End If

            If ds.Tables(0).Rows.Count = 0 Then
                Exit Function
            End If

            Dim row As Integer = Me.dgv1.CurrentCell.RowIndex

            If Me.dgv1.RowCount > 0 And Me.dgv1.CurrentRow.Index < Me.dgv1.Rows.Count - 1 Then
                Me.dgv1.CurrentCell = Me.dgv1.Rows(row + 1).Cells(0)
                If Me.TabControl1.SelectedIndex = 1 Then
                    If ActionHelp = IDServer.IDServer.ActionBtn.ActionHelp Then
                        FillText(Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value, Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value)
                    End If
                End If
            End If
        Catch ex As Exception
            MsgBox("ActionGoNext：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    Public Function ActionGoPrevious() As Boolean Implements IDServer.IDServer.IDServer.ActionGoPrevious
        Try
            If ds Is Nothing Then
                Exit Function
            End If

            If ds.Tables.Count = 0 Then
                Exit Function
            End If

            If ds.Tables(0).Rows.Count = 0 Then
                Exit Function
            End If

            Dim row As Integer = Me.dgv1.CurrentCell.RowIndex

            If Me.dgv1.RowCount > 0 And Me.dgv1.CurrentRow.Index > 0 Then
                Me.dgv1.CurrentCell = Me.dgv1.Rows(row - 1).Cells(0)
                If Me.TabControl1.SelectedIndex = 1 Then
                    If ActionHelp = IDServer.IDServer.ActionBtn.ActionHelp Then
                        FillText(Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value, Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value)
                    End If
                End If
            End If
        Catch ex As Exception
            MsgBox("ActionGoPrevious：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    Public Function ActionImport() As Boolean Implements IDServer.IDServer.IDServer.ActionImport

    End Function

    Public Function ActionInitialize() As Boolean Implements IDServer.IDServer.IDServer.ActionInitialize
        Try
            Dim sql As String = ""
            Dim DsNew As New DataSet

            Dim CurrencyID_f, CurrencyID_t, dates As String
            Dim Rate, RateWGJ As Double


            '年份不能為空
            If Me.txtYears.Text.Trim & "" = "" Then
                MsgBox(GetMsg("BSC", "YearsIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                Me.txtYears.Focus()
                Return False
            End If

            '月份不能為空
            If Me.cmbMonths.Text.Length <= 0 Then
                MsgBox(GetMsg("BSC", "MonthsIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                Me.cmbMonths.Focus()
                Return False
            End If

            If (dgv3.Rows.Count = 1 Or dgv3.Rows(0).IsNewRow = True) And (dgv4.Rows.Count = 1 Or dgv4.Rows(0).IsNewRow = True) Then
                MsgBox(My.Resources.ZeroRecord, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                dgv3.Focus()

                Return False
            End If

            '檢查DGV3
            For I As Integer = 0 To dgv3.Rows.Count - 1
                If dgv3.Rows(I).IsNewRow = False Then
                    If dgv3.Rows(I).Cells("CurrencyID_f").Value & "" = "" Then
                        MsgBox(GetMsg("BSC", "CurrencyIDIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv3.Focus()
                        dgv3.CurrentCell = dgv3.Rows(I).Cells("CurrencyID_f")
                        Return False

                    End If

                    If dgv3.Rows(I).Cells("CurrencyID_t").Value & "" = "" Then
                        MsgBox(GetMsg("BSC", "CurrencyIDIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv3.Focus()
                        dgv3.CurrentCell = dgv3.Rows(I).Cells("CurrencyID_t")
                        Return False

                    End If

                    If dgv3.Rows(I).Cells("Rate").Value Is Nothing Or dgv3.Rows(I).Cells("Rate").Value Is DBNull.Value _
                           Or Val(dgv3.Rows(I).Cells("Rate").Value) = 0 Then
                        MsgBox(GetMsg("BSC", "RateIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv3.Focus()
                        dgv3.CurrentCell = dgv3.Rows(I).Cells("Rate")
                        Return False

                    End If

                    If dgv3.Rows(I).Cells("RateWGJ").Value Is Nothing Or dgv3.Rows(I).Cells("RateWGJ").Value Is DBNull.Value Then
                        dgv3.Rows(I).Cells("RateWGJ").Value = 0

                    End If

                    CurrencyID_f = GetCurrencyID(dgv3.Rows(I).Cells("CurrencyID_f").Value)
                    CurrencyID_t = GetCurrencyID(dgv3.Rows(I).Cells("CurrencyID_t").Value)
                    Rate = Double.Parse(dgv3.Rows(I).Cells("Rate").Value)
                    RateWGJ = Double.Parse(dgv3.Rows(I).Cells("RateWGJ").Value)
                    If CurrencyID_f = CurrencyID_t And Rate <> 1 Then
                        MsgBox(GetMsg("BSC", "RateIsSame"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv3.Focus()
                        dgv3.CurrentCell = dgv3.Rows(I).Cells("Rate")
                        Return False
                    End If

                    If CurrencyID_f = CurrencyID_t And RateWGJ <> 1 And RateWGJ > 0 Then
                        MsgBox(GetMsg("BSC", "RateIsSame"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv3.Focus()
                        dgv3.CurrentCell = dgv3.Rows(I).Cells("RateWGJ")
                        Return False
                    End If

                End If
            Next


            If (dgv3.Rows.Count = 1 And dgv3.Rows(0).IsNewRow = False) Or dgv3.Rows.Count > 1 Then
                '檢查是否有重復記錄
                sql = "DECLARE @Count1 INT,@Count2 INT ;"
                sql += "CREATE TABLE #t1 ("
                sql = sql & "   [CurrencyID_f] [char](10) NOT NULL,"
                sql = sql & "   [CurrencyID_t] [char](10) NOT NULL);"

                For I As Integer = 0 To dgv3.Rows.Count - 1
                    If dgv3.Rows(I).IsNewRow = False Then
                        CurrencyID_f = GetCurrencyID(dgv3.Rows(I).Cells("CurrencyID_f").Value)
                        CurrencyID_t = GetCurrencyID(dgv3.Rows(I).Cells("CurrencyID_t").Value)
                       
                        sql += "INSERT INTO #T1 VALUES ('" & CurrencyID_f & "','" & CurrencyID_t & "');"

                    End If
                Next

                sql += "SELECT @Count1 = Count(* )"
                sql = sql & " FROM   #t1;"
                sql += "SELECT @Count2 = Count(* )"
                sql = sql & " FROM   (SELECT DISTINCT currencyid_f,"
                sql = sql & "                         currencyid_t"
                sql = sql & "         FROM   #t1) a;"
                sql += "SELECT CASE "
                sql = sql & "          WHEN @Count1 = @Count2 THEN 1"
                sql = sql & "          ELSE 0"
                sql = sql & "        END;"

                If Int32.Parse(GetValue(sql)) = 0 Then
                    MsgBox(GetMsg("BSC", "RateIsReplace"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                    dgv3.Focus()

                End If

            End If



            '檢查DGV4
            For I As Integer = 0 To dgv4.Rows.Count - 1
                If dgv4.Rows(I).IsNewRow = False Then
                    If dgv4.Rows(I).Cells("CurrencyID_f").Value & "" = "" Then
                        MsgBox(GetMsg("BSC", "CurrencyIDIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv4.Focus()
                        dgv4.CurrentCell = dgv4.Rows(I).Cells("CurrencyID_f")
                        Return False

                    End If

                    If dgv4.Rows(I).Cells("CurrencyID_t").Value & "" = "" Then
                        MsgBox(GetMsg("BSC", "CurrencyIDIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv4.Focus()
                        dgv4.CurrentCell = dgv4.Rows(I).Cells("CurrencyID_t")
                        Return False

                    End If

                    If dgv4.Rows(I).Cells("Rate").Value Is Nothing Or Val(dgv4.Rows(I).Cells("Rate").Value) = 0 Then
                        MsgBox(GetMsg("BSC", "RateIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv4.Focus()
                        dgv4.CurrentCell = dgv4.Rows(I).Cells("Rate")
                        Return False

                    End If

                    If dgv4.Rows(I).Cells("dates").Value & "" = "" Or dgv4.Rows(I).Cells("dates").Value & "" = "0" _
                       Or CheckDay(Val(Me.txtYears.Text), Val(Me.cmbMonths.Text), Val(dgv4.Rows(I).Cells("dates").Value)) = False Then
                        MsgBox(GetMsg("BSC", "datesIsNull"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv4.Focus()
                        dgv4.CurrentCell = dgv4.Rows(I).Cells("dates")
                        Return False

                    End If

                    CurrencyID_f = GetCurrencyID(dgv4.Rows(I).Cells("CurrencyID_f").Value)
                    CurrencyID_t = GetCurrencyID(dgv4.Rows(I).Cells("CurrencyID_t").Value)

                    Rate = Double.Parse(dgv4.Rows(I).Cells("Rate").Value)

                    If CurrencyID_f = CurrencyID_t And Rate <> 1 Then
                        MsgBox(GetMsg("BSC", "RateIsSame"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                        dgv4.Focus()
                        dgv4.CurrentCell = dgv4.Rows(I).Cells("Rate")
                        Return False
                    End If


                End If
            Next


            If (dgv4.Rows.Count = 1 And dgv4.Rows(0).IsNewRow = False) Or dgv4.Rows.Count > 1 Then
                '檢查是否有重復記錄
                sql = "DECLARE @Count1 INT,@Count2 INT ;"
                sql += "CREATE TABLE #t1 ("
                sql += "	[dates] [char](2) NOT NULL,"
                sql = sql & "   [CurrencyID_f] [char](10) NOT NULL,"
                sql = sql & "   [CurrencyID_t] [char](10) NOT NULL);"

                For I As Integer = 0 To dgv4.Rows.Count - 1
                    If dgv4.Rows(I).IsNewRow = False Then

                        CurrencyID_f = GetCurrencyID(dgv4.Rows(I).Cells("CurrencyID_f").Value)
                        CurrencyID_t = GetCurrencyID(dgv4.Rows(I).Cells("CurrencyID_t").Value)
                        dates = dgv4.Rows(I).Cells("dates").Value

                        sql += "INSERT INTO #T1 VALUES ('" & dates & "','" & CurrencyID_f & "','" & CurrencyID_t & "');"

                    End If
                Next

                sql += "SELECT @Count1 = Count(* )"
                sql = sql & " FROM   #t1;"
                sql += "SELECT @Count2 = Count(* )"
                sql = sql & " FROM   (SELECT DISTINCT dates,currencyid_f,"
                sql = sql & "                         currencyid_t"
                sql = sql & "         FROM   #t1) a;"
                sql += "SELECT CASE "
                sql = sql & "          WHEN @Count1 = @Count2 THEN 1"
                sql = sql & "          ELSE 0"
                sql = sql & "        END;"

                If Int32.Parse(GetValue(sql)) = 0 Then
                    MsgBox(GetMsg("BSC", "RateIsReplace"), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
                    dgv4.Focus()

                End If

            End If

           
            Return True
        Catch ex As Exception
            MsgBox("ActionInitialize：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    Public Function ActionPrint() As Boolean Implements IDServer.IDServer.IDServer.ActionPrint

    End Function

    Public Function ActionSave() As Boolean Implements IDServer.IDServer.IDServer.ActionSave
        Try
            If ActionInitialize() = False Then Exit Function

            If BtnActionSave() = True Then
                IsFind = FindRecord()
                SetButton(ButtonType.Browse, IsFind)

                Return True
            Else

                Return False
            End If

        Catch ex As Exception
            MsgBox("ActionSave:" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    Public Function ActionSelect() As Boolean Implements IDServer.IDServer.IDServer.ActionSelect

    End Function

    Private Function BtnActionSave() As Boolean

        If ds Is Nothing Then
            Initialize()
        End If

        If ds.Tables.Count = 0 Then
            Initialize()
        End If

        Dim Years, Months, CurrencyID_f, CurrencyID_t, dates As String
        Dim Rate, RateWGJ As Double

        Dim cnSqlServer As New SqlConnection
        cnSqlServer = GetConn()
        Dim cmdSqlServer As New SqlCommand
        Dim trnSqlServer As SqlTransaction = cnSqlServer.BeginTransaction



        Try
            Dim sql As String = ""

            Years = Me.txtYears.Text.Trim & ""
            Months = Me.cmbMonths.Text & ""

            '當是添加保存
            If ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Then

                With cmdSqlServer
                    .Connection = cnSqlServer
                    .Transaction = trnSqlServer
                    .CommandType = CommandType.Text


                    '向BSC_RateA插入記錄
                    If dgv3.Rows.Count = 1 Then
                        sql+ = "INSERT INTO BSC_RateA (Years, Months, CurrencyID_f, CurrencyID_t, Rate) VALUES ('" & _
                              Years & "','" & Months & "','" & BWB & "','" & BWB & "',1);"
                    Else
                        For I As Integer = 0 To dgv3.Rows.Count - 1
                            If dgv3.Rows(I).IsNewRow = False Then

                                CurrencyID_f = GetCurrencyID(dgv3.Rows(I).Cells("CurrencyID_f").Value)
                                CurrencyID_t = GetCurrencyID(dgv3.Rows(I).Cells("CurrencyID_t").Value)
                                Rate = Double.Parse(dgv3.Rows(I).Cells("Rate").Value)
                                RateWGJ = Double.Parse(dgv3.Rows(I).Cells("RateWGJ").Value)

                                sql += "INSERT INTO BSC_RateA (Years, Months, CurrencyID_f, CurrencyID_t, Rate,RateWGJ) VALUES ('" & _
                                       Years & "','" & Months & "','" & CurrencyID_f & "','" & CurrencyID_t & "'," & _
                                       Rate & "," & RateWGJ & ");"

                            End If
                        Next
                    End If

                    '向BSC_RateB插入記錄
                    For I As Integer = 0 To dgv4.Rows.Count - 1
                        If dgv4.Rows(I).IsNewRow = False Then
                            dates = dgv4.Rows(I).Cells("dates").Value
                            CurrencyID_f = GetCurrencyID(dgv4.Rows(I).Cells("CurrencyID_f").Value)
                            CurrencyID_t = GetCurrencyID(dgv4.Rows(I).Cells("CurrencyID_t").Value)
                            Rate = Double.Parse(dgv4.Rows(I).Cells("Rate").Value)

                            sql += "INSERT INTO BSC_RateB (Years, Months, dates, CurrencyID_f, CurrencyID_t, Rate) VALUES ('" & _
                                     Years & "','" & Months & "','" & dates & "','" & CurrencyID_f & "','" & CurrencyID_t & "'," & _
                                     Rate & ");"

                        End If
                    Next

                    .CommandText = sql
                    .ExecuteNonQuery()

                End With

                'AddDataSet
                'ds.tables(0)

                If dgv3.Rows.Count = 1 Then
                    Dim mRow As DataRow
                    mRow = ds.Tables(0).NewRow
                    mRow("Years") = Years
                    mRow("Months") = Months
                    mRow("CurrencyID_f") = GetCurrency(BWB)
                    mRow("CurrencyID_t") = GetCurrency(BWB)
                    mRow("Rate") = 1
                    ds.Tables(0).Rows.Add(mRow)
                    ds.AcceptChanges()

                Else
                    For I As Integer = 0 To dgv3.Rows.Count - 1
                        If dgv3.Rows(I).IsNewRow = False Then

                            CurrencyID_f = dgv3.Rows(I).Cells("CurrencyID_f").Value
                            CurrencyID_t = dgv3.Rows(I).Cells("CurrencyID_t").Value
                            Rate = Double.Parse(dgv3.Rows(I).Cells("Rate").Value)
                            RateWGJ = Double.Parse(dgv3.Rows(I).Cells("RateWGJ").Value)

                            Dim mRow As DataRow
                            mRow = ds.Tables(0).NewRow
                            mRow("Years") = Years
                            mRow("Months") = Months
                            mRow("CurrencyID_f") = CurrencyID_f
                            mRow("CurrencyID_t") = CurrencyID_t
                            mRow("Rate") = Rate
                            mRow("RateWGJ") = RateWGJ
                            ds.Tables(0).Rows.Add(mRow)
                            ds.AcceptChanges()
                            
                        End If
                    Next
                End If

                ''ds.tables(1)
                'For I As Integer = 0 To dgv4.Rows.Count - 1
                '    If dgv4.Rows(I).IsNewRow = False Then

                '        dates = dgv4.Rows(I).Cells("dates").Value
                '        CurrencyID_f = dgv4.Rows(I).Cells("CurrencyID_f").Value
                '        CurrencyID_t = dgv4.Rows(I).Cells("CurrencyID_t").Value
                '        Rate = Double.Parse(dgv4.Rows(I).Cells("Rate").Value)

                '        Dim mRow As DataRow
                '        mRow = ds.Tables(1).NewRow
                '        mRow("Years") = Years
                '        mRow("Months") = Months
                '        mRow("dates") = dates
                '        mRow("CurrencyID_f") = CurrencyID_f
                '        mRow("CurrencyID_t") = CurrencyID_t
                '        mRow("Rate") = Rate
                '        ds.Tables(1).Rows.Add(mRow)
                '        ds.AcceptChanges()

                '    End If
                'Next

                
            Else

                With cmdSqlServer
                    .Connection = cnSqlServer
                    .Transaction = trnSqlServer
                    .CommandType = CommandType.Text


                    '1.先刪除原記錄
                    sql += "DELETE BSC_RateA WHERE Years='" & Years & "' AND Months='" & Months & "';"
                    sql += "DELETE BSC_RateB WHERE Years='" & Years & "' AND Months='" & Months & "';"


                    '向BSC_RateA插入記錄
                    If dgv3.Rows.Count = 1 Then
                        sql += "INSERT INTO BSC_RateA (Years, Months, CurrencyID_f, CurrencyID_t, Rate) VALUES ('" & _
                              Years & "','" & Months & "','" & BWB & "','" & BWB & "',1);"
                    Else
                        For I As Integer = 0 To dgv3.Rows.Count - 1
                            If dgv3.Rows(I).IsNewRow = False Then

                                CurrencyID_f = GetCurrencyID(dgv3.Rows(I).Cells("CurrencyID_f").Value)
                                CurrencyID_t = GetCurrencyID(dgv3.Rows(I).Cells("CurrencyID_t").Value)
                                Rate = Double.Parse(dgv3.Rows(I).Cells("Rate").Value)
                                RateWGJ = Double.Parse(dgv3.Rows(I).Cells("RateWGJ").Value)

                                sql += "INSERT INTO BSC_RateA (Years, Months, CurrencyID_f, CurrencyID_t, Rate,RateWGJ) VALUES ('" & _
                                       Years & "','" & Months & "','" & CurrencyID_f & "','" & CurrencyID_t & "'," & _
                                       Rate & "," & RateWGJ & ");"

                            End If
                        Next
                    End If

                    '向BSC_RateB插入記錄
                    For I As Integer = 0 To dgv4.Rows.Count - 1
                        If dgv4.Rows(I).IsNewRow = False Then
                            dates = dgv4.Rows(I).Cells("dates").Value
                            CurrencyID_f = GetCurrencyID(dgv4.Rows(I).Cells("CurrencyID_f").Value)
                            CurrencyID_t = GetCurrencyID(dgv4.Rows(I).Cells("CurrencyID_t").Value)
                            Rate = Double.Parse(dgv4.Rows(I).Cells("Rate").Value)

                            sql += "INSERT INTO BSC_RateB (Years, Months, dates, CurrencyID_f, CurrencyID_t, Rate) VALUES ('" & _
                                     Years & "','" & Months & "','" & dates & "','" & CurrencyID_f & "','" & CurrencyID_t & "'," & _
                                     Rate & ");"

                        End If
                    Next

                    .CommandText = sql
                    .ExecuteNonQuery()

                End With

                '先刪除原先的記錄
                For I As Integer = ds.Tables(0).Rows.Count - 1 To 0 Step -1
                    Dim mRow As DataRow
                    mRow = ds.Tables(0).Rows(I)
                    If mRow.Item("Years").ToString.Trim = Years And mRow.Item("Months").ToString.Trim = Months Then
                        mRow.Delete()
                        ds.AcceptChanges()

                    End If
                Next


                'AddDataSet
                'ds.tables(0)

                If dgv3.Rows.Count = 1 Then
                    Dim mRow As DataRow
                    mRow = ds.Tables(0).NewRow
                    mRow("Years") = Years
                    mRow("Months") = Months
                    mRow("CurrencyID_f") = GetCurrency(BWB)
                    mRow("CurrencyID_t") = GetCurrency(BWB)
                    mRow("Rate") = 1
                    ds.Tables(0).Rows.Add(mRow)
                    ds.AcceptChanges()

                Else
                    For I As Integer = 0 To dgv3.Rows.Count - 1
                        If dgv3.Rows(I).IsNewRow = False Then

                            CurrencyID_f = dgv3.Rows(I).Cells("CurrencyID_f").Value
                            CurrencyID_t = dgv3.Rows(I).Cells("CurrencyID_t").Value
                            Rate = Double.Parse(dgv3.Rows(I).Cells("Rate").Value)
                            RateWGJ = Double.Parse(dgv3.Rows(I).Cells("RateWGJ").Value)

                            Dim mRow As DataRow
                            mRow = ds.Tables(0).NewRow
                            mRow("Years") = Years
                            mRow("Months") = Months
                            mRow("CurrencyID_f") = CurrencyID_f
                            mRow("CurrencyID_t") = CurrencyID_t
                            mRow("Rate") = Rate
                            mRow("RateWGJ") = RateWGJ
                            ds.Tables(0).Rows.Add(mRow)
                            ds.AcceptChanges()

                        End If
                    Next
                End If


            End If
            EnabledControls(False)

            trnSqlServer.Commit()


            MsgBox(IIf(ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd, My.resources.Addsuccess, My.resources.EditSuccess), MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)
            ActionHelp = IDServer.IDServer.ActionBtn.ActionHelp
            Me.dgv1_CurrentCellChanged(Nothing, Nothing)
            Return True
        Catch ex As Exception
            trnSqlServer.Rollback()
            If ex.Message.Split("違反 PRIMARY KEY 條件約束 'PK_BSC_RateA'。無法在物件 'dbo.BSC_RateA' 中插入重複的索引鍵。").Length = 2 Then
                MsgBox(GetMsg("BSC", "RateIsExists"), MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, Msg.CompanyName)
            Else
                MsgBox("BtnActionSave:" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
            End If
            Return False


        End Try

    End Function


#End Region

#Region "Windows Sub"

    Private Sub Frm_Rate_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            ReBindControls()
            ReadDgrdStyle(Me)
            EnabledControls(False)
            setDgvEdit()

            Me.PYears = ""
            Me.PMonths = ""

            BWB = dsBSC.Tables("BWB").Rows(0).Item(0)

            'AddKeyDown(GroupBox1)
            AddKeyDown(GroupBox2)
            Me.btnFind.Enabled = FindEnable
        Catch ex As Exception
            MsgBox("Frm_Rate_Load：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try

    End Sub

    Private Sub mnuSaveStyle_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSaveStyle.Click
        SaveDgrdStyle(Me)
    End Sub

    Private Sub mnuDelStyle_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuDelStyle.Click
        DeldgvStyle(Me)
    End Sub

    Private Sub cmbCurrencyID1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCurrencyID1.SelectedIndexChanged
        Try
            If Me.cmbCurrencyID1.Visible = False Then
                Exit Sub
            End If

            dgv3.CurrentCell.Tag = Me.cmbCurrencyID1.SelectedValue
            dgv3.CurrentCell.Value = Me.cmbCurrencyID1.Text
        Catch ex As Exception
            If ex.Message = "Object reference not set to an instance of an object." Or ex.Message = "未将对象引用设置到对象的实例。" Or ex.Message = "並未將物件參考設定為物件的執行個體" Then
                Exit Sub
            Else
                MsgBox("cmbCurrencyID1_SelectedIndexChanged：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
            End If

        End Try
    End Sub

    Private Sub cmbCurrencyID2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCurrencyID2.SelectedIndexChanged
        Try
            If Me.cmbCurrencyID2.Visible = False Then
                Exit Sub
            End If

            dgv4.CurrentCell.Tag = Me.cmbCurrencyID2.SelectedValue
            dgv4.CurrentCell.Value = Me.cmbCurrencyID2.Text
        Catch ex As Exception
            If ex.Message = "Object reference not set to an instance of an object." Or ex.Message = "未将对象引用设置到对象的实例。" Or ex.Message = "並未將物件參考設定為物件的執行個體" Then
                Exit Sub
            Else
                MsgBox("cmbCurrencyID2_SelectedIndexChanged：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
            End If

        End Try
    End Sub

    Private Sub ToolStripMenuItemAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemAdd.Click
        Try
            If Not (ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit) Then
                Exit Sub
            End If

            Dim Years, Months As String
            Dim RowIndex, RowCount As Int16
            Years = Me.txtYears.Text & ""
            Months = Me.cmbMonths.Text & ""


            If dgv3.CurrentCell IsNot Nothing Then
                RowIndex = dgv3.CurrentRow.Index
                RowCount = dgv3.Rows.Count

                '判斷當前行是否為最後一行
                If RowIndex = RowCount - 1 Then
                    dgv3.Rows.Add()
                Else
                    dgv3.Rows.Insert(RowIndex, 1)
                End If

            End If


        Catch ex As Exception
            MsgBox("ToolStripMenuItemAdd_Click：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    Private Sub ToolStripMenuItemDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemDel.Click
        Try
            If Not (ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit) Then
                Exit Sub
            End If

            Dim Years, Months As String
            Dim RowIndex, RowCount As Int16

            If dgv3.CurrentCell IsNot Nothing Then
                RowIndex = dgv3.CurrentRow.Index

                '判斷當前行是否為新增行
                If dgv3.CurrentRow.IsNewRow = False Then
                    dgv3.Rows.RemoveAt(RowIndex)
                End If

            End If


        Catch ex As Exception
            MsgBox("ToolStripMenuItemDel_Click：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    Private Sub ToolStripMenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem2.Click
        Try
            If Not (ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit) Then
                Exit Sub
            End If

            Dim Years, Months As String
            Dim RowIndex, RowCount As Int16
            Years = Me.txtYears.Text & ""
            Months = Me.cmbMonths.Text & ""

            If dgv4.CurrentCell IsNot Nothing Then
                RowIndex = dgv4.CurrentRow.Index
                RowCount = dgv4.Rows.Count

                '判斷當前行是否為最後一行
                If RowIndex = RowCount - 1 Then
                    dgv4.Rows.Add()
                Else
                    dgv4.Rows.Insert(RowIndex, 1)
                End If

            End If


        Catch ex As Exception
            MsgBox("ToolStripMenuItem2_Click：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    Private Sub ToolStripMenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem4.Click
        Try
            If Not (ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit) Then
                Exit Sub
            End If

            Dim Years, Months As String
            Dim RowIndex, RowCount As Int16

            If dgv4.CurrentCell IsNot Nothing Then
                RowIndex = dgv4.CurrentRow.Index

                '判斷當前行是否為新增行
                If dgv4.CurrentRow.IsNewRow = False Then
                    dgv4.Rows.RemoveAt(RowIndex)
                End If

            End If

        Catch ex As Exception
            MsgBox("ToolStripMenuItem4_Click：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Filter()
    End Sub

    Private Sub TabControl1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
        Try
            If ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit Then
                Exit Sub
            End If
            If ds.Tables.Count = 0 Then
                Exit Sub
            End If

            If ds.Tables(0).Rows.Count = 0 Then
                Exit Sub
            End If

            Dim Years As String
            Dim Months As String

            If Me.TabControl1.SelectedIndex = 1 Then
                'Me.TabControl1.SelectedIndex = 1
                If IsDBNull(Me.dgv1.Rows(0).Cells(0).Value) Then
                    Exit Sub
                Else
                    Years = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value
                    Months = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value

                    'If Not (ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit) Then
                    FillText(Years, Months)
                    'End If

                End If

            End If
        Catch ex As Exception
            MsgBox("TabControl1_SelectedIndexChanged：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try

    End Sub

#End Region

#Region "Function Define"
    ''' <summary>
    ''' 獲取公共資料
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub GetPublic()
        Try

            Dim Table(5) As String
            Dim sql(5) As String

            '來源幣別1
            Table(0) = "CurrencyID_f1"
            sql(0) = "select '' ListCode,'' ListA union select ListCode,ListA from dbo.BSC_OtherDefineB " & _
                     " where ItemNo=(select Top 1 ID from dbo.BSC_OtherDefineA where ItemCode='CurrencyDefine') " & _
                     " ORDER BY ListCode"

            '目的幣別1
            Table(1) = "CurrencyID_t1"
            sql(1) = "select '' ListCode,'' ListA union select ListCode,ListA from dbo.BSC_OtherDefineB " & _
                     " where ItemNo=(select Top 1 ID from dbo.BSC_OtherDefineA where ItemCode='CurrencyDefine') " & _
                     " ORDER BY ListCode"

            '幣別1
            Table(2) = "CurrencyID1"
            sql(2) = "select ListCode,ListA from dbo.BSC_OtherDefineB " & _
                     " where ItemNo=(select Top 1 ID from dbo.BSC_OtherDefineA where ItemCode='CurrencyDefine') " & _
                     " ORDER BY ListCode"

            '幣別2
            Table(3) = "CurrencyID2"
            sql(3) = "select ListCode,ListA from dbo.BSC_OtherDefineB " & _
                     " where ItemNo=(select Top 1 ID from dbo.BSC_OtherDefineA where ItemCode='CurrencyDefine') " & _
                     " ORDER BY ListCode"

            '本位幣
            Table(4) = "BWB"
            sql(4) = "SELECT ISNULL(FValue,'HKD') AS BWB FROM dbo.[BSC_SystemProfile] " & _
                     "   WHERE FCategory='System' AND FKey='Standard money' "

            '幣別2
            Table(5) = "CurrencyID"
            sql(5) = "select ListCode,ListA from dbo.BSC_OtherDefineB " & _
                     " where ItemNo=(select Top 1 ID from dbo.BSC_OtherDefineA where ItemCode='CurrencyDefine') " & _
                     " ORDER BY ListCode"


            dsBSC = GetDataSet(sql, Table)

        Catch ex As Exception
            MsgBox("GetPublic：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try

    End Sub

    ''' <summary>
    ''' 綁定單元格並設定標題
    ''' </summary>
    ''' <param name="dgv">要綁定標題的DataGridView</param>
    ''' <param name="Index">序號</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function BindDgv(ByVal dgv As DataGridView, ByVal Index As Integer)
        Try
            Dim tb As New DataTable
            Dim mTable As New DataTable
            Dim Hash As New Hashtable

            With tb
                .Columns.Add("colName")
                .Columns.Add("colCaption")
            End With

            With tb.Rows
                Select Case Index
                    Case 1
                        .Add("Years", "年份")
                        .Add("Months", "月份")
                        .Add("CurrencyID_f", "來源幣別")
                        .Add("CurrencyID_t", "目的幣別")
                        .Add("Rate", "匯率")
                        .Add("RateWGJ", "外管局匯率")

                    Case 2
                        .Add("Years", "年份")
                        .Add("Months", "月份")
                        .Add("dates", "日期")
                        .Add("CurrencyID_f", "來源幣別")
                        .Add("CurrencyID_t", "目的幣別")
                        .Add("Rate", "匯率")

                    Case 3
                        .Add("Years", "年份")
                        .Add("Months", "月份")
                        .Add("CurrencyID_f", "來源幣別")
                        .Add("CurrencyID_t", "目的幣別")
                        .Add("Rate", "匯率")
                        .Add("RateWGJ", "外管局匯率")

                    Case 4
                        .Add("Years", "年份")
                        .Add("Months", "月份")
                        .Add("dates", "日期")
                        .Add("CurrencyID_f", "來源幣別")
                        .Add("CurrencyID_t", "目的幣別")
                        .Add("Rate", "匯率")

                End Select
            End With

            With mTable
                For I As Integer = 0 To tb.Rows.Count - 1
                    .Columns.Add(tb.Rows(I).Item(0))
                Next
            End With

            dgv.DataSource = mTable

            With Hash
                For I As Integer = 0 To tb.Rows.Count - 1
                    Hash.Add(tb.Rows(I).Item(0), tb.Rows(I).Item(1))
                Next
            End With

            For I As Integer = 0 To dgv.Columns.Count - 1
                Dim colName, colCaption As String
                colName = mTable.Columns(I).ColumnName
                dgv.Columns(colName).HeaderText = Hash.Item(colName)
                dgv.Columns(I).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            Next


        Catch ex As Exception
            MsgBox("BindDgv：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    ''' <summary>
    ''' 重新綁定控制項的資料源
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ReBindControls()
        Try
            GetPublic()

            '綁定數據

            With Me.cmbCurrencyID_f1
                .DataSource = dsBSC.Tables("CurrencyID_f1")
                .ValueMember = ("ListCode")
                .DisplayMember = ("ListA")
            End With

            With Me.cmbCurrencyID_t1
                .DataSource = dsBSC.Tables("CurrencyID_t1")
                .ValueMember = ("ListCode")
                .DisplayMember = ("ListA")
            End With

            With Me.cmbCurrencyID1
                .DataSource = dsBSC.Tables("CurrencyID1")
                .ValueMember = ("ListCode")
                .DisplayMember = ("ListA")
            End With

            With Me.cmbCurrencyID2
                .DataSource = dsBSC.Tables("CurrencyID2")
                .ValueMember = ("ListCode")
                .DisplayMember = ("ListA")
            End With

            BindDgv(dgv1, 1)

            With Me.dgv1
                .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                .AllowUserToOrderColumns = True
                .MultiSelect = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .ReadOnly = True
            End With

            BindDgv(dgv2, 2)
            With Me.dgv2
                .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                .AllowUserToOrderColumns = True
                .MultiSelect = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .ReadOnly = True
            End With

            setDgvCol(dgv3, 3)
            With Me.dgv3
                .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                .AllowUserToOrderColumns = True
                .MultiSelect = False
                .AllowUserToAddRows = True
                .AllowUserToDeleteRows = False
                .ReadOnly = False
                .EditMode = DataGridViewEditMode.EditOnEnter
            End With

            setDgvCol(dgv4, 4)
            With Me.dgv4
                .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                .AllowUserToOrderColumns = True
                .MultiSelect = False
                .AllowUserToAddRows = True
                .AllowUserToDeleteRows = False
                .ReadOnly = False
                .EditMode = DataGridViewEditMode.EditOnEnter
            End With

            With Me.cmbMonths1.Items
                .Add("")
                For I As Integer = 1 To 12
                    .Add(I.ToString)
                Next
            End With

            With Me.cmbMonths.Items
                .Add("")
                For I As Integer = 1 To 12
                    .Add(I.ToString)
                Next
            End With

            FormatDgvCell()

        Catch ex As Exception
            MsgBox("ReBindControls：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try

    End Sub

    ''' <summary>
    ''' 格式化單元格
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub FormatDgvCell()
        Try
            Dim N4cell As DataGridViewCell = New DataGridViewTextBoxCell
            With N4cell
                .Style.Format = "N4"
                .Style.NullValue = 0
                .Style.Alignment = DataGridViewContentAlignment.MiddleRight

            End With

            Dim N5cell As DataGridViewCell = New DataGridViewTextBoxCell
            With N5cell
                .Style.Format = "N5"
                .Style.NullValue = 0
                .Style.Alignment = DataGridViewContentAlignment.MiddleRight

            End With


            With Me.dgv1
                .Columns("Rate").CellTemplate = N4cell
                .Columns("RateWGJ").CellTemplate = N5cell
            End With

            With Me.dgv2
                .Columns("Rate").CellTemplate = N4cell
            End With

            With Me.dgv3
                .Columns("Rate").CellTemplate = N4cell
                .Columns("RateWGJ").CellTemplate = N5cell
            End With

            With Me.dgv4
                .Columns("Rate").CellTemplate = N4cell
            End With



        Catch ex As Exception
            MsgBox("FormatDgvCell：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' 設置DataGridView的列標題
    ''' </summary>
    ''' <param name="dgv">DataGridView</param>
    ''' <param name="Index">序號</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function setDgvCol(ByVal dgv As DataGridView, ByVal Index As Integer)
        Try
            Dim tb As New DataTable
            Dim mTable As New DataTable
            Dim Hash As New Hashtable

            With tb
                .Columns.Add("colName")
                .Columns.Add("colCaption")
            End With

            With tb.Rows
                Select Case Index
                    Case 1
                        .Add("Years", "年份")
                        .Add("Months", "月份")
                        .Add("CurrencyID_f", "來源幣別")
                        .Add("CurrencyID_t", "目的幣別")
                        .Add("Rate", "匯率")
                        .Add("RateWGJ", "外管局匯率")

                    Case 2
                        .Add("Years", "年份")
                        .Add("Months", "月份")
                        .Add("dates", "日期")
                        .Add("CurrencyID_f", "來源幣別")
                        .Add("CurrencyID_t", "目的幣別")
                        .Add("Rate", "匯率")

                    Case 3
                        .Add("Years", "年份")
                        .Add("Months", "月份")
                        .Add("CurrencyID_f", "來源幣別")
                        .Add("CurrencyID_t", "目的幣別")
                        .Add("Rate", "匯率")
                        .Add("RateWGJ", "外管局匯率")

                    Case 4
                        .Add("Years", "年份")
                        .Add("Months", "月份")
                        .Add("dates", "日期")
                        .Add("CurrencyID_f", "來源幣別")
                        .Add("CurrencyID_t", "目的幣別")
                        .Add("Rate", "匯率")

                End Select
            End With

            For I As Integer = 0 To tb.Rows.Count - 1
                dgv.Columns.Add(tb.Rows(I).Item("colName"), tb.Rows(I).Item("colCaption"))
            Next

        Catch ex As Exception
            MsgBox("setDgvCol：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

    ''' <summary>
    ''' 設置控件的Enable屬性
    ''' </summary>
    ''' <param name="B"></param>
    ''' <remarks></remarks>
    Private Sub EnabledControls(ByVal B As Boolean)
        Try
            Me.GroupBox2.Enabled = B
            dgv3.ReadOnly = Not B
            dgv4.ReadOnly = Not B

            If B = False Then
                Me.cmbCurrencyID1.Visible = B
                Me.cmbCurrencyID2.Visible = B
            End If

        Catch ex As Exception
            MsgBox("EnabledControls：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try

    End Sub

    ''' <summary>
    ''' 設置按鈕狀態
    ''' </summary>
    ''' <param name="type"></param>
    ''' <remarks></remarks>
    Private Sub SetButton(ByVal type As ButtonType, ByVal IsFind As Boolean)
        Try
            Select Case type
                Case ButtonType.Add
                    With MainFrom
                        .ToolStripAdd.Enabled = False And AddEnable
                        .ToolStripEdit.Enabled = False And EditEnable
                        .ToolStripDelete.Enabled = False And DeleteEnable
                        .ToolStripSave.Enabled = True
                        .ToolStripCancel.Enabled = True
                        .ToolStripFirst.Enabled = False
                        .ToolStripLast.Enabled = False
                        .ToolStripNext.Enabled = False
                        .ToolStripPrivous.Enabled = False
                        .ToolStripExport.Enabled = IsFind And ExportEnable
                        .ToolStripImport.Enabled = True And ImportEnable
                        .ToolStripPrint.Enabled = IsFind And PrintEnable
                        .ToolStripFilter.Enabled = True
                        .ToolStripCheck.Enabled = False
                        .ToolStripUnCheck.Enabled = False

                    End With

                Case ButtonType.Browse
                    With MainFrom
                        .ToolStripAdd.Enabled = True And AddEnable
                        .ToolStripEdit.Enabled = IsFind And EditEnable
                        .ToolStripDelete.Enabled = IsFind And DeleteEnable
                        .ToolStripSave.Enabled = False
                        .ToolStripCancel.Enabled = False
                        .ToolStripFirst.Enabled = IsFind
                        .ToolStripLast.Enabled = IsFind
                        .ToolStripNext.Enabled = IsFind
                        .ToolStripPrivous.Enabled = IsFind
                        .ToolStripExport.Enabled = IsFind
                        .ToolStripImport.Enabled = True And ImportEnable
                        .ToolStripPrint.Enabled = IsFind And PrintEnable
                        .ToolStripFilter.Enabled = True
                        .ToolStripCheck.Enabled = False
                        .ToolStripUnCheck.Enabled = False

                    End With

                Case ButtonType.Cancel
                    With MainFrom
                        .ToolStripAdd.Enabled = True And AddEnable
                        .ToolStripEdit.Enabled = IsFind And EditEnable
                        .ToolStripDelete.Enabled = IsFind And DeleteEnable
                        .ToolStripSave.Enabled = False
                        .ToolStripCancel.Enabled = False
                        .ToolStripFirst.Enabled = IsFind
                        .ToolStripLast.Enabled = IsFind
                        .ToolStripNext.Enabled = IsFind
                        .ToolStripPrivous.Enabled = IsFind
                        .ToolStripExport.Enabled = IsFind
                        .ToolStripImport.Enabled = True And ImportEnable
                        .ToolStripPrint.Enabled = IsFind And PrintEnable
                        .ToolStripFilter.Enabled = True
                        .ToolStripCheck.Enabled = False
                        .ToolStripUnCheck.Enabled = False

                    End With

                Case ButtonType.Edit
                    With MainFrom
                        .ToolStripAdd.Enabled = False And AddEnable
                        .ToolStripEdit.Enabled = False And EditEnable
                        .ToolStripDelete.Enabled = False And DeleteEnable
                        .ToolStripSave.Enabled = True
                        .ToolStripCancel.Enabled = True
                        .ToolStripFirst.Enabled = False
                        .ToolStripLast.Enabled = False
                        .ToolStripNext.Enabled = False
                        .ToolStripPrivous.Enabled = False
                        .ToolStripExport.Enabled = IsFind
                        .ToolStripImport.Enabled = True And ImportEnable
                        .ToolStripPrint.Enabled = IsFind And PrintEnable
                        .ToolStripFilter.Enabled = True
                        .ToolStripCheck.Enabled = False
                        .ToolStripUnCheck.Enabled = False

                    End With

            End Select
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try

    End Sub

    ''' <summary>
    ''' ClearText
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ClearText()
        Try
            Me.txtYears.Text = ""
            Me.cmbMonths.Text = ""
            If dgv3.Rows.Count > 0 Or dgv3.Rows(0).IsNewRow = False Then
                dgv3.Rows.Clear()

            End If

            If dgv4.Rows.Count > 0 Or dgv4.Rows(0).IsNewRow = False Then
                dgv4.Rows.Clear()

            End If

        Catch ex As Exception
            MsgBox("ClearText：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' 判斷是否有記錄
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function FindRecord() As Boolean
        Try
            If ds Is Nothing Then
                Return False
                Exit Function
            End If

            If Me.dgv1.Rows.Count = 0 Then
                Return False
            Else
                Return True

            End If
        Catch ex As Exception
            MsgBox("FindRecord：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try


    End Function

    Private Sub setDgvEdit()
        Try
            With dgv3
                .Columns("Years").Visible = False
                .Columns("Months").Visible = False
                .Columns("CurrencyID_f").ReadOnly = True
                .Columns("CurrencyID_t").ReadOnly = True
            End With

            With dgv4
                .Columns("Years").Visible = False
                .Columns("Months").Visible = False
                .Columns("CurrencyID_f").ReadOnly = True
                .Columns("CurrencyID_t").ReadOnly = True
            End With

        Catch ex As Exception
            MsgBox("setDgvEdit：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' 過濾符合條件的記錄
    ''' </summary>
    ''' <remarks></remarks>
    Sub Filter()
        Try
            If ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit Then
                Exit Sub
            End If

            Me.Cursor = Cursors.WaitCursor

            Dim Table(1) As String
            Dim sql(1) As String

            sql(0) = "SELECT a.years,"
            sql(0) = sql(0) & "        a.months,"
            sql(0) = sql(0) & "        currencyid_f = b.lista,"
            sql(0) = sql(0) & "        currencyid_t = c.lista,"
            sql(0) = sql(0) & "        rate,"
            sql(0) = sql(0) & "        ratewgj"
            sql(0) = sql(0) & " FROM   bsc_ratea a,"
            sql(0) = sql(0) & "        (SELECT listcode,"
            sql(0) = sql(0) & "                lista"
            sql(0) = sql(0) & "         FROM   dbo.bsc_otherdefineb"
            sql(0) = sql(0) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(0) = sql(0) & "                          FROM   dbo.bsc_otherdefinea"
            sql(0) = sql(0) & "                          WHERE  itemcode = 'CurrencyDefine')) b,"
            sql(0) = sql(0) & "        (SELECT listcode,"
            sql(0) = sql(0) & "                lista"
            sql(0) = sql(0) & "         FROM   dbo.bsc_otherdefineb"
            sql(0) = sql(0) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(0) = sql(0) & "                          FROM   dbo.bsc_otherdefinea"
            sql(0) = sql(0) & "                          WHERE  itemcode = 'CurrencyDefine')) c"
            sql(0) = sql(0) & " WHERE  a.currencyid_f = b.listcode"
            sql(0) = sql(0) & "        AND a.currencyid_t = c.listcode "

            If Me.txtYears1.Text.Trim <> "" Then
                sql(0) += " AND a.years='" & Me.txtYears1.Text & "' "
            End If

            If Me.cmbMonths1.Text <> "" Then
                sql(0) += " AND a.months='" & Me.cmbMonths1.Text & "' "
            End If

            If Me.cmbCurrencyID_f1.Text <> "" Then
                sql(0) += " AND a.currencyid_f='" & Me.cmbCurrencyID_f1.SelectedValue & "' "
            End If

            If Me.cmbCurrencyID_t1.Text <> "" Then
                sql(0) += " AND a.currencyid_t='" & Me.cmbCurrencyID_t1.SelectedValue & "' "
            End If

            sql(0) += " Order by a.Years, a.Months "

            sql(1) = "SELECT a.years,"
            sql(1) = sql(1) & "        a.months,"
            sql(1) = sql(1) & "        a.dates,"
            sql(1) = sql(1) & "        currencyid_f = b.lista,"
            sql(1) = sql(1) & "        currencyid_t = c.lista,"
            sql(1) = sql(1) & "        rate"
            sql(1) = sql(1) & " FROM   bsc_rateb a,"
            sql(1) = sql(1) & "        (SELECT listcode,"
            sql(1) = sql(1) & "                lista"
            sql(1) = sql(1) & "         FROM   dbo.bsc_otherdefineb"
            sql(1) = sql(1) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(1) = sql(1) & "                          FROM   dbo.bsc_otherdefinea"
            sql(1) = sql(1) & "                          WHERE  itemcode = 'CurrencyDefine')) b,"
            sql(1) = sql(1) & "        (SELECT listcode,"
            sql(1) = sql(1) & "                lista"
            sql(1) = sql(1) & "         FROM   dbo.bsc_otherdefineb"
            sql(1) = sql(1) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(1) = sql(1) & "                          FROM   dbo.bsc_otherdefinea"
            sql(1) = sql(1) & "                          WHERE  itemcode = 'CurrencyDefine')) c"
            sql(1) = sql(1) & " WHERE  a.currencyid_f = b.listcode"
            sql(1) = sql(1) & "        AND a.currencyid_t = c.listcode"

            If Me.txtYears1.Text.Trim <> "" Then
                sql(1) += " AND a.years='" & Me.txtYears1.Text & "' "
            End If

            If Me.cmbMonths1.Text <> "" Then
                sql(1) += " AND a.months='" & Me.cmbMonths1.Text & "' "
            End If

            If Me.cmbCurrencyID_f1.Text <> "" Then
                sql(1) += " AND a.currencyid_f='" & Me.cmbCurrencyID_f1.SelectedValue & "' "
            End If

            If Me.cmbCurrencyID_t1.Text <> "" Then
                sql(1) += " AND a.currencyid_t='" & Me.cmbCurrencyID_t1.SelectedValue & "' "
            End If

            sql(1) += " Order by a.Years, a.Months,a.dates "

            Table(0) = "t_Master"
            Table(1) = "t_Detail"


            ds = GetDataSet(sql, Table)

            Me.dgv1.DataSource = ds.Tables("t_Master")
            bFind = (ds.Tables(0).Rows.Count > 0)
            If ds.Tables("t_Master").Rows.Count > 0 Then
                Me.dgv1.CurrentCell = Me.dgv1.Rows(0).Cells(2)
                Me.dgv1.CurrentCell = Me.dgv1.Rows(0).Cells(0)
            Else
                Me.dgv2.DataSource = ds.Tables("t_Detail")
                MsgBox(Msg.NotFindRecord, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, Msg.CompanyName)

            End If

            IsFind = FindRecord()
            SetButton(ButtonType.Browse, IsFind)

            If IsFind = True Then
                Me.dgv1_CurrentCellChanged(Nothing, Nothing)
            End If


        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox("Filter:" + ex.ToString, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, Msg.CompanyName)

        Finally
            Me.Cursor = Cursors.Default
        End Try


    End Sub

    ''' <summary>
    ''' 根據輸入的日期判斷其是否合法
    ''' </summary>
    ''' <param name="Year">年份</param>
    ''' <param name="Month">月份</param>
    ''' <param name="Day">日期</param>
    ''' <returns>合法則返回True,非法返回False</returns>
    ''' <remarks></remarks>
    Private Function CheckDay(ByVal Year As Int16, ByVal Month As Int16, ByVal Day As Int16) As Boolean
        Try
            Dim intDates As Int16
            Dim Date1 As DateTime = Convert.ToDateTime(Year.ToString + "-" + Month.ToString + "-" + "1")
            Dim Date2 As DateTime
            Date2 = DateAdd(DateInterval.Month, 1, Date1)
            intDates = DateDiff(DateInterval.Day, Date1, Date2)

            If Day < 1 Or Day > intDates Then
                Return False
            Else
                Return True
            End If
        Catch ex As Exception
            MsgBox("CheckDay：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try

    End Function

    ''' <summary>
    ''' 根據幣別名稱返回幣別代碼
    ''' </summary>
    ''' <param name="Currency">幣別名稱</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCurrencyID(ByVal Currency As String) As String
        Try
            Dim CurrencyID As String
            If dsBSC Is Nothing Then
                GetPublic()
            End If

            Dim tb As DataTable = dsBSC.Tables("CurrencyID").Copy
            Dim rows() As DataRow = tb.Select("ListA='" & Currency & "'")
            If rows.Length > 0 Then
                For Each row As DataRow In rows
                    CurrencyID = row.Item("ListCode")
                Next
            End If

            Return CurrencyID
        Catch ex As Exception
            MsgBox("GetCurrencyID：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
            Return ""
        End Try
    End Function

    ''' <summary>
    ''' 根據幣別代碼返回幣別名稱
    ''' </summary>
    ''' <param name="Currency">幣別代碼</param>
    ''' <returns>幣別名稱</returns>
    ''' <remarks></remarks>
    Private Function GetCurrency(ByVal Currency As String) As String
        Try
            Dim CurrencyID As String
            If dsBSC Is Nothing Then
                GetPublic()
            End If

            Dim tb As DataTable = dsBSC.Tables("CurrencyID").Copy
            Dim rows() As DataRow = tb.Select("ListCode='" & Currency & "'")
            If rows.Length > 0 Then
                For Each row As DataRow In rows
                    CurrencyID = row.Item("ListA")
                Next
            End If

            Return CurrencyID
        Catch ex As Exception
            MsgBox("GetCurrency：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
            Return ""
        End Try
    End Function

    ''' <summary>
    ''' 根據選擇的資料填充DataGridView
    ''' </summary>
    ''' <param name="Years">年份</param>
    ''' <param name="Months">月份</param>
    ''' <remarks></remarks>
    Private Sub FillText(ByVal Years As String, ByVal Months As String)
        Try
            ClearText()
            Dim Table(1) As String
            Dim sql(1) As String
            Dim NewDs As New DataSet
            Dim tb As New DataTable

            sql(0) = "SELECT "
            sql(0) = sql(0) & "        currencyid_f = b.lista,"
            sql(0) = sql(0) & "        currencyid_t = c.lista,"
            sql(0) = sql(0) & "        rate,"
            sql(0) = sql(0) & "        ratewgj"
            sql(0) = sql(0) & " FROM   bsc_ratea a,"
            sql(0) = sql(0) & "        (SELECT listcode,"
            sql(0) = sql(0) & "                lista"
            sql(0) = sql(0) & "         FROM   dbo.bsc_otherdefineb"
            sql(0) = sql(0) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(0) = sql(0) & "                          FROM   dbo.bsc_otherdefinea"
            sql(0) = sql(0) & "                          WHERE  itemcode = 'CurrencyDefine')) b,"
            sql(0) = sql(0) & "        (SELECT listcode,"
            sql(0) = sql(0) & "                lista"
            sql(0) = sql(0) & "         FROM   dbo.bsc_otherdefineb"
            sql(0) = sql(0) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(0) = sql(0) & "                          FROM   dbo.bsc_otherdefinea"
            sql(0) = sql(0) & "                          WHERE  itemcode = 'CurrencyDefine')) c"
            sql(0) = sql(0) & " WHERE  a.currencyid_f = b.listcode"
            sql(0) = sql(0) & "        AND a.currencyid_t = c.listcode "

            sql(0) += " AND a.years='" & Years & "' "
            sql(0) += " AND a.months='" & Months & "' "
            sql(0) += " Order by a.Years, a.Months "

            sql(1) = "SELECT "
            sql(1) = sql(1) & "        a.dates,"
            sql(1) = sql(1) & "        currencyid_f = b.lista,"
            sql(1) = sql(1) & "        currencyid_t = c.lista,"
            sql(1) = sql(1) & "        rate"
            sql(1) = sql(1) & " FROM   bsc_rateb a,"
            sql(1) = sql(1) & "        (SELECT listcode,"
            sql(1) = sql(1) & "                lista"
            sql(1) = sql(1) & "         FROM   dbo.bsc_otherdefineb"
            sql(1) = sql(1) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(1) = sql(1) & "                          FROM   dbo.bsc_otherdefinea"
            sql(1) = sql(1) & "                          WHERE  itemcode = 'CurrencyDefine')) b,"
            sql(1) = sql(1) & "        (SELECT listcode,"
            sql(1) = sql(1) & "                lista"
            sql(1) = sql(1) & "         FROM   dbo.bsc_otherdefineb"
            sql(1) = sql(1) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(1) = sql(1) & "                          FROM   dbo.bsc_otherdefinea"
            sql(1) = sql(1) & "                          WHERE  itemcode = 'CurrencyDefine')) c"
            sql(1) = sql(1) & " WHERE  a.currencyid_f = b.listcode"
            sql(1) = sql(1) & "        AND a.currencyid_t = c.listcode"

            sql(1) += " AND a.years='" & Years & "' "
            sql(1) += " AND a.months='" & Months & "' "
            sql(1) += " Order by a.Years, a.Months,a.dates "

            Table(0) = "t_Master"
            Table(1) = "t_Detail"


            NewDs = GetDataSet(sql, Table)

            tb = NewDs.Tables(0).Copy()
            For I As Integer = 0 To tb.Rows.Count - 1
                Dim colName As String
                dgv3.Rows.Insert(I, 1)
                For J As Integer = 0 To tb.Columns.Count - 1
                    colName = tb.Columns(J).ColumnName
                    dgv3.Rows(I).Cells(colName).Value = tb.Rows(I).Item(colName)

                Next
            Next

            tb = NewDs.Tables(1).Copy()
            For I As Integer = 0 To tb.Rows.Count - 1
                Dim colName As String
                dgv4.Rows.Insert(I, 1)
                For J As Integer = 0 To tb.Columns.Count - 1
                    colName = tb.Columns(J).ColumnName
                    dgv4.Rows(I).Cells(colName).Value = tb.Rows(I).Item(colName)

                Next
            Next

            Me.txtYears.Text = Years
            Me.cmbMonths.SelectedIndex = Convert.ToInt32(Months)


        Catch ex As Exception
            MsgBox("FillText：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    '''  初始化ds
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function Initialize()
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim Table(1) As String
            Dim sql(1) As String

            sql(0) = "SELECT a.years,"
            sql(0) = sql(0) & "        a.months,"
            sql(0) = sql(0) & "        currencyid_f = b.lista,"
            sql(0) = sql(0) & "        currencyid_t = c.lista,"
            sql(0) = sql(0) & "        rate,"
            sql(0) = sql(0) & "        ratewgj"
            sql(0) = sql(0) & " FROM   bsc_ratea a,"
            sql(0) = sql(0) & "        (SELECT listcode,"
            sql(0) = sql(0) & "                lista"
            sql(0) = sql(0) & "         FROM   dbo.bsc_otherdefineb"
            sql(0) = sql(0) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(0) = sql(0) & "                          FROM   dbo.bsc_otherdefinea"
            sql(0) = sql(0) & "                          WHERE  itemcode = 'CurrencyDefine')) b,"
            sql(0) = sql(0) & "        (SELECT listcode,"
            sql(0) = sql(0) & "                lista"
            sql(0) = sql(0) & "         FROM   dbo.bsc_otherdefineb"
            sql(0) = sql(0) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(0) = sql(0) & "                          FROM   dbo.bsc_otherdefinea"
            sql(0) = sql(0) & "                          WHERE  itemcode = 'CurrencyDefine')) c"
            sql(0) = sql(0) & " WHERE  1=2"
          
            sql(1) = "SELECT a.years,"
            sql(1) = sql(1) & "        a.months,"
            sql(1) = sql(1) & "        a.dates,"
            sql(1) = sql(1) & "        currencyid_f = b.lista,"
            sql(1) = sql(1) & "        currencyid_t = c.lista,"
            sql(1) = sql(1) & "        rate"
            sql(1) = sql(1) & " FROM   bsc_rateb a,"
            sql(1) = sql(1) & "        (SELECT listcode,"
            sql(1) = sql(1) & "                lista"
            sql(1) = sql(1) & "         FROM   dbo.bsc_otherdefineb"
            sql(1) = sql(1) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(1) = sql(1) & "                          FROM   dbo.bsc_otherdefinea"
            sql(1) = sql(1) & "                          WHERE  itemcode = 'CurrencyDefine')) b,"
            sql(1) = sql(1) & "        (SELECT listcode,"
            sql(1) = sql(1) & "                lista"
            sql(1) = sql(1) & "         FROM   dbo.bsc_otherdefineb"
            sql(1) = sql(1) & "         WHERE  itemno = (SELECT Top 1  id"
            sql(1) = sql(1) & "                          FROM   dbo.bsc_otherdefinea"
            sql(1) = sql(1) & "                          WHERE  itemcode = 'CurrencyDefine')) c"
            sql(1) = sql(1) & " WHERE  1=2 "

            Table(0) = "t_Master"
            Table(1) = "t_Detail"

            ds = GetDataSet(sql, Table)

            Me.dgv1.DataSource = ds.Tables("t_Master")
            bFind = (ds.Tables(0).Rows.Count > 0)
            If ds.Tables("t_Master").Rows.Count > 0 Then
                Me.dgv1.CurrentCell = Me.dgv1.Rows(0).Cells(2)
                Me.dgv1.CurrentCell = Me.dgv1.Rows(0).Cells(0)
            Else
                Me.dgv2.DataSource = ds.Tables("t_Detail")

            End If

            IsFind = FindRecord()
          
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox("Initialize：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        Finally
            Me.Cursor = Cursors.Default

        End Try

    End Function

#End Region

#Region "DataGridView sub"

    Private Sub dgv1_RowPostPaint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgv1.RowPostPaint, _
          dgv2.RowPostPaint, dgv3.RowPostPaint, dgv4.RowPostPaint
        AddDgvRowNumber(CType(sender, DataGridView), sender, e)
    End Sub

    Private Sub dgv1_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles dgv1.DataError, _
                  dgv2.DataError, dgv3.DataError, dgv4.DataError
        Try

        Catch ex As Exception
            Exit Sub
        End Try
    End Sub

    Private Sub dgv3_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgv3.CurrentCellChanged
        Try
            If ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit Then
                If dgv3.Columns(dgv3.CurrentCell.ColumnIndex).Name = "CurrencyID_f" Or dgv3.Columns(dgv3.CurrentCell.ColumnIndex).Name = "CurrencyID_t" Then
                    Dim Rect As Rectangle = Me.dgv3.GetCellDisplayRectangle(Me.dgv3.CurrentCell.ColumnIndex, Me.dgv3.CurrentCell.RowIndex, False)
                    Dim CurrencyValue As String = ""
                    If Me.dgv3.CurrentCell.Value IsNot Nothing Then
                        If Me.dgv3.CurrentCell.Value IsNot DBNull.Value Then
                            CurrencyValue = Me.dgv3.CurrentCell.Value.ToString
                        End If
                    End If

                    cmbCurrencyID1.Left = Rect.Left + Me.dgv3.Left
                    cmbCurrencyID1.Top = Rect.Top + Me.dgv3.Top
                    cmbCurrencyID1.Width = Rect.Width
                    cmbCurrencyID1.Height = Rect.Height
                    cmbCurrencyID1.Visible = True

                    If CurrencyValue <> "" Then
                        cmbCurrencyID1.Text = dgv3.CurrentCell.Value
                    End If

                    'cmbCurrencyID1.Focus()

                Else
                    cmbCurrencyID1.Visible = False
                End If
            End If
        Catch ex As Exception
            If ex.Message = "Object reference not set to an instance of an object." Then
                Exit Sub
            Else
                MsgBox("dgv3_CurrentCellChanged:" + ex.ToString, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, Msg.CompanyName)
            End If

        End Try

    End Sub

    Private Sub dgv4_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgv4.CurrentCellChanged
        Try
            If ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit Then
                If dgv4.Columns(dgv4.CurrentCell.ColumnIndex).Name = "CurrencyID_f" Or dgv4.Columns(dgv4.CurrentCell.ColumnIndex).Name = "CurrencyID_t" Then
                    Dim Rect As Rectangle = Me.dgv4.GetCellDisplayRectangle(dgv4.CurrentCell.ColumnIndex, dgv4.CurrentCell.RowIndex, False)
                    Dim CurrencyValue As String = ""
                    If Me.dgv4.CurrentCell.Value IsNot Nothing Then
                        If Me.dgv4.CurrentCell.Value IsNot DBNull.Value Then
                            CurrencyValue = Me.dgv4.CurrentCell.Value.ToString
                        End If
                    End If

                    cmbCurrencyID2.Left = Rect.Left + Me.dgv4.Left
                    cmbCurrencyID2.Top = Rect.Top + Me.dgv4.Top
                    cmbCurrencyID2.Width = Rect.Width
                    cmbCurrencyID2.Height = Rect.Height
                    cmbCurrencyID2.Visible = True

                    If CurrencyValue <> "" Then
                        cmbCurrencyID2.Text = dgv4.CurrentCell.Value
                    End If

                    'cmbCurrencyID2.Focus()

                Else
                    cmbCurrencyID2.Visible = False
                End If
            End If
        Catch ex As Exception
            If ex.Message = "Object reference not set to an instance of an object." Then
                Exit Sub
            Else
                MsgBox("dgv4_CurrentCellChanged：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
            End If
        End Try
    End Sub

    Private Sub dgv1_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgv1.CurrentCellChanged
        Try

            Me.Cursor = Cursors.WaitCursor
            If Not (ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit) Then
                Dim mytb As New DataTable
                Dim sql As String

                sql = "SELECT a.years,"
                sql = sql & "        a.months,"
                sql = sql & "        a.dates,"
                sql = sql & "        currencyid_f = b.lista,"
                sql = sql & "        currencyid_t = c.lista,"
                sql = sql & "        rate"
                sql = sql & " FROM   bsc_rateb a,"
                sql = sql & "        (SELECT listcode,"
                sql = sql & "                lista"
                sql = sql & "         FROM   dbo.bsc_otherdefineb"
                sql = sql & "         WHERE  itemno = (SELECT Top 1  id"
                sql = sql & "                          FROM   dbo.bsc_otherdefinea"
                sql = sql & "                          WHERE  itemcode = 'CurrencyDefine')) b,"
                sql = sql & "        (SELECT listcode,"
                sql = sql & "                lista"
                sql = sql & "         FROM   dbo.bsc_otherdefineb"
                sql = sql & "         WHERE  itemno = (SELECT Top 1  id"
                sql = sql & "                          FROM   dbo.bsc_otherdefinea"
                sql = sql & "                          WHERE  itemcode = 'CurrencyDefine')) c"
                sql = sql & " WHERE  a.currencyid_f = b.listcode"
                sql = sql & "        AND a.currencyid_t = c.listcode "

                Dim Years As String
                Dim Months As String

                If Me.dgv1.DataSource Is Nothing Then
                    Exit Sub
                End If

                If Me.dgv1.SelectedCells.Count <= 0 Then
                    Exit Sub

                End If

                If Me.dgv1.CurrentCell Is Nothing Then
                    Exit Sub
                End If

                Years = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value.ToString.Trim & ""
                Months = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value.ToString.Trim & ""

                IsFind = FindRecord()

                If Years Is Nothing Then
                    Exit Sub
                Else

                End If

                If Me.PYears = Years And Me.PMonths = Months Then
                    Exit Sub
                End If

                sql += " AND Years='" & Years & "' AND Months='" & Months & "'"
                sql += " ORDER BY a.Years, a.Months,a.dates"
                tempDS = ReturnDataset(sql)


                Me.dgv2.DataSource = tempDS.Tables(0)

                Me.PYears = Years
                Me.PMonths = Months

            End If

        Catch ex As Exception
            MsgBox("dgv1_CurrentCellChanged：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)

        Finally
            SetButton(ButtonType.Browse, IsFind)
            Me.Cursor = Cursors.Default

        End Try
    End Sub

    Private Sub dgv3_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgv3.CellFormatting
        FormatDgvCell()
    End Sub

    Private Sub dgv3_CellValidated(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv3.CellValidated
        Try
            If ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit Then
                If dgv3.Columns(dgv3.CurrentCell.ColumnIndex).Name = "Rate" Then
                    If dgv3.CurrentCell.Value IsNot Nothing Then
                        If IsNumeric(dgv3.CurrentCell.Value) Then
                            If Val(dgv3.CurrentCell.Value) < 0 Then
                                dgv3.CurrentCell.Value = 0

                            End If
                        Else
                            dgv3.CurrentCell.Value = 0
                        End If

                        If Val(dgv3.CurrentCell.Value) > 0 Then
                            If dgv3.CurrentCell.Value.ToString.Length > 1 Then
                                If dgv3.CurrentCell.Value.ToString.Substring(0, 1) = "0" And dgv3.CurrentCell.Value.ToString.Substring(0, 2) <> "0." Then
                                    dgv3.CurrentCell.Value = 0

                                End If
                            End If

                        End If


                    End If



                End If

                If dgv3.Columns(dgv3.CurrentCell.ColumnIndex).Name = "RateWGJ" Then
                    If dgv3.CurrentCell.Value IsNot Nothing Then
                        If IsNumeric(dgv3.CurrentCell.Value) Then
                            If Val(dgv3.CurrentCell.Value) < 0 Then
                                dgv3.CurrentCell.Value = 0

                            End If
                        Else
                            dgv3.CurrentCell.Value = 0
                        End If

                        If Val(dgv3.CurrentCell.Value) > 0 Then
                            If dgv3.CurrentCell.Value.ToString.Length > 1 Then
                                If dgv3.CurrentCell.Value.ToString.Substring(0, 1) = "0" And dgv3.CurrentCell.Value.ToString.Substring(0, 2) <> "0." Then
                                    dgv3.CurrentCell.Value = 0

                                End If
                            End If

                        End If

                    End If


                End If
            End If
        Catch ex As Exception
            MsgBox("dgv3_CellValidated：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    Private Sub dgv4_CellValidated(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv4.CellValidated
        Try
            If ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit Then
                If dgv4.Columns(dgv4.CurrentCell.ColumnIndex).Name = "Rate" Then
                    If dgv4.CurrentCell.Value IsNot Nothing Then
                        If IsNumeric(dgv4.CurrentCell.Value) Then
                            If Val(dgv4.CurrentCell.Value) < 0 Then
                                dgv4.CurrentCell.Value = 0

                            End If
                        Else
                            dgv4.CurrentCell.Value = 0
                        End If

                        If Val(dgv4.CurrentCell.Value) > 0 Then
                            If dgv4.CurrentCell.Value.ToString.Length > 1 Then
                                If dgv4.CurrentCell.Value.ToString.Substring(0, 1) = "0" And dgv4.CurrentCell.Value.ToString.Substring(0, 2) <> "0." Then
                                    dgv4.CurrentCell.Value = 0

                                End If
                            End If

                        End If

                    End If

                End If

                If dgv4.Columns(dgv4.CurrentCell.ColumnIndex).Name = "dates" Then
                    If dgv4.CurrentCell.Value IsNot Nothing Then
                        If IsNumeric(dgv4.CurrentCell.Value) Then
                            If Val(dgv4.CurrentCell.Value) < 0 Or Val(dgv4.CurrentCell.Value) > 31 Then
                                dgv4.CurrentCell.Value = 0

                            End If
                        Else
                            dgv4.CurrentCell.Value = 0
                        End If

                        If Val(dgv4.CurrentCell.Value) > 0 Then
                            If dgv4.CurrentCell.Value.ToString.Length > 1 Then
                                If dgv4.CurrentCell.Value.ToString.Substring(0, 1) = "0" And dgv4.CurrentCell.Value.ToString.Substring(0, 2) <> "0." Then
                                    dgv4.CurrentCell.Value = 0

                                End If
                            End If

                        End If
                    End If

                End If

            End If

        Catch ex As Exception
            MsgBox("dgv4_CellValidated：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    Private Sub dgv1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgv1.DoubleClick
        Try
            If ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit Then
                Exit Sub
            End If
            If ds.Tables.Count = 0 Then
                Exit Sub
            End If

            If ds.Tables(0).Rows.Count = 0 Then
                Exit Sub
            End If

            Dim Years As String
            Dim Months As String

            If Me.TabControl1.SelectedIndex = 0 Then
                'Me.TabControl1.SelectedIndex = 1
                If IsDBNull(Me.dgv1.Rows(0).Cells(0).Value) Then
                    Exit Sub
                Else
                    Years = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Years").Value
                    Months = Me.dgv1.Rows(Me.dgv1.CurrentRow.Index).Cells("Months").Value

                    'If Not (ActionHelp = IDServer.IDServer.ActionBtn.ActionAdd Or ActionHelp = IDServer.IDServer.ActionBtn.ActionEdit) Then
                    FillText(Years, Months)
                    'End If


                End If
                Me.TabControl1.SelectedIndex = 1
            End If
        Catch ex As Exception
            MsgBox("dgv1_DoubleClick：" + ex.ToString, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

#End Region


End Class
